<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Instructor_model extends CI_Model {
	
	public function __construct()
    {
        parent::__construct();
    }
	
	public function check_username($uname)
	{
		$this->load->database();
		$sql = "SELECT username FROM instructor_account WHERE username = ?";
		$query = $this->db->query($sql, $uname);
	
		if ($query->num_rows() > 0)
			return true;
		return false;
	}
	
	public function check_login($account)
	{
		$this->load->database();
		$sql = "SELECT username,password FROM instructor_account WHERE username = ? AND password = ?"; 
		$query = $this->db->query($sql, array($account['username'], $account['password']));
		
		if($query->num_rows() == 0)
			$query = NULL;
		
		return $query;
	}

	public function add_account($account)
	{
		$this->load->database();
		$sql = "INSERT INTO instructor_account(emp_id,name,username,password,approved,activated) VALUES (?,?,?,?,?,?)";
		$query = $this->db->query($sql, array($account['emp_id'], $account['name'],
		$account['username'], $account['password'], $account['approved'], $account['activated']));	
		return $query;
	}

	public function getUnapproved() {
		$this->load->database();
		$sql = "SELECT username FROM encoder_account WHERE approved = 0";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	public function search_by_name($input)
	{
		$this->load->database();
		//$sql = "SELECT name, student_no FROM student_account where name like ?";
		$this->db->select('name, student_no');
		$this->db->like('name', $input);
		$query = $this->db->get('student_account');
		//$query = $this->db->query($sql, $input);
		return $query->result_array();	
	}
	
	public function search_by_student_number($input)
	{
		$this->load->database();
		//$sql = "SELECT name, student_no FROM student_account";
		$this->db->select('name, student_no');
		$this->db->like('student_no', $input, 'none');
		$query = $this->db->get('student_account');
		//$query = $this->db->query($sql, $input);
		return $query->result_array();	
	}
	
	public function get_inst_data($data)
	{
		$this->load->database();
		$this->db->select('username, name, emp_id, mobile_number, designation, rank, faculty_room');
		$this->db->like('username', $data);
		$query = $this->db->get('instructor_account');
		return $query->result_array();
	}
	
	public function course_exists($course)
	{
		$this->load->database();
		$sql = "SELECT course FROM prerequisite WHERE course = ?";
		$query = $this->db->query($sql, $course);
		$existing = $query->result_array();
		if(sizeof($existing) == 0)
		{
			return FALSE;
		}
		else
		{
			return TRUE;
		}
	}
	
	public function get_qualified_students($course)
	{
		$this->load->database();
		if($course == "CMSC 11")
		{
			$sql = "SELECT student_no, name from student_account s, subjects_taken st WHERE st.grade <= '3.0' AND course_no_fk = 'MATH 11' AND s.student_no = st.student_no_fk OR st.grade <= '3.0' AND course_no_fk = 'MATH 17' AND s.student_no = st.student_no_fk";
			$query = $this->db->query($sql);
			return $query->result_array();
		}
		else
		{
			$sql = "SELECT prerequisite FROM prerequisite WHERE course = ?";
			$query = $this->db->query($sql, $course);
			$prereq = $query->result_array();
			
			for($i=0;$i<sizeof($prereq);$i++)
			{
				if(implode($prereq[$i]) == 'COI')
				{
					unset($prereq[$i]);
					$result = array_values($prereq);
				}
			}
			
			if(sizeof($prereq) == 1)
			{
				if(implode($prereq[0]) == NULL)
				{
					$result = NULL;
				}
				else
				{
					$sql = "SELECT student_no, name from student_account s, subjects_taken st WHERE st.grade <= '3.0' AND course_no_fk = ? AND s.student_no = st.student_no_fk";
					$query = $this->db->query($sql, implode($prereq[0]));
					$result = $query->result_array();
				}
			}
			else if(sizeof($prereq) == 2)
			{
				$sql = "SELECT student_no, name from student_account s, subjects_taken st, subjects_taken st2 WHERE (st.grade <= '3.0' AND st.course_no_fk = ? AND s.student_no = st.student_no_fk) AND (st2.grade <= '3.0' AND st2.course_no_fk = ? AND s.student_no = st2.student_no_fk)";
				$query = $this->db->query($sql, array(implode($prereq[0]), implode($prereq[1])));
				$result = $query->result_array();
			}
			return $result;
		}
	}
	
	public function get_emp_id($username)
	{
		$this->load->database();
		$this->db->select('emp_id');
		$this->db->where('username', $username);
		$query = $this->db->get('instructor_account');
		return $query->result_array();
	}
	
	public function get_name($username)
	{
		$this->load->database();
		$this->db->select('name');
		$this->db->where('username', $username);
		$query = $this->db->get('instructor_account');
		return $query->result_array();
	}
	
	public function get_previous_sp_advisees($emp_id)
	{
		$this->load->database();
		$sql = "SELECT DISTINCT student_no, s.name, emp_id FROM student_account s, prev_sp_adviser psp, instructor_account i WHERE  psp.emp_no = ? AND s.student_no = psp.stud_no_fk ORDER BY psp.prev_sp_adviser_id DESC";
		$query = $this->db->query($sql, $emp_id);
		return $query->result_array();
	}
	
	public function get_current_sp_advisees($emp_id)
	{
		$this->load->database();
		$sql = "SELECT student_no, name FROM student_account WHERE sp_adviser = ? ORDER BY name";
		$query = $this->db->query($sql, $emp_id);
		return $query->result_array();
	}
	
	public function get_registration_advisees($name)
	{
		$this->load->database();
		$sql = "SELECT student_no, name FROM student_account WHERE reg_adviser = ? ORDER BY name";
		$query = $this->db->query($sql, $name);
		return $query->result_array();
	}
	
	public function get_inst_name($emp_id)
	{
		$this->load->database();
		$this->db->select('name');
		$this->db->where('emp_id', $emp_id);
		$query = $this->db->get('instructor_account');
		return $query->result_array();
	
	}

	public function getActivatedStatus() {
		$this->load->database();
		$this->db->select('username, activated');
		$this->db->where('approved', 1);
		$query = $this->db->get('instructor_account');

		return $query->result_array();
	}

	public function getAllUsernames() {
		$this->load->database();
		$this->db->select('username');
		$this->db->where('approved', 1);
		$query = $this->db->get('instructor_account');

		return $query->result_array();
	}

	public function delete_account($username) {
		$this->load->database();
		$this->db->where('username', $username);
		$this->db->delete('instructor_account');
	}
}